﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class JddxDAL:IJddxDAL
    {
        public int AddJddx(JddxInfo dx)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, dx.Num),
                DALHelper.MakeInParam(":jday", OracleType.VarChar, dx.Jday),
                DALHelper.MakeInParam(":banbie", OracleType.VarChar, dx.Banbie),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dx.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dx.Tianbao),
                DALHelper.MakeInParam(":kn", OracleType.Number, dx.Kn),
                DALHelper.MakeInParam(":kw", OracleType.Number, dx.Kw),
                DALHelper.MakeInParam(":zn", OracleType.Number, dx.Zn),
                DALHelper.MakeInParam(":hx", OracleType.Number, dx.Hx)
            };
            return (DALHelper.RunCommand(@"insert into dd.JDDX values (:num, to_date(:jday,'yyyy-mm-dd hh24:mi:ss'), :banbie, :banzu, :tianbao, :kn, :kw, :zn, :hx)", prams));
        }
        public int UpdateJddx(string num, string banbie, string banzu, int kn, int kw, int zn, decimal hx)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                
                DALHelper.MakeInParam(":banbie", OracleType.VarChar, banbie),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, banzu),
                
                DALHelper.MakeInParam(":kn", OracleType.Number, kn),
                DALHelper.MakeInParam(":kw", OracleType.Number, kw),
                DALHelper.MakeInParam(":zn", OracleType.Number, zn),
                DALHelper.MakeInParam(":hx", OracleType.Number, hx)
            };
            return (DALHelper.RunCommand(@"update dd.JDDX set banbie = :banbie, banzu = :banzu, kn = :kn, kw = :kw, zn = :zn, hx = :hx where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.JDDX where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.JDDX where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday)
        {
            string btime = sday + "00:00:00";
            string etime = sday + "23:59:59";
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":btime", OracleType.VarChar, btime),
                DALHelper.MakeInParam(":etime", OracleType.VarChar, etime)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.JDDX where jday >= to_date(:btime,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:etime,'yyyy-mm-dd hh24:mi:ss') order by jday desc", prams, "Sbyday"));
        }
        public DataSet jddxqk()
        {
            return (DALHelper.RunProcReturn(@"select * from (select kn, kw, zn from dd.JDDX  order by jday desc) where rownum < 2", "dxqk"));
        }
        public DataSet jddxqkH(string bt, string et)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select * from (select kn, kw, zn from dd.JDDX where jday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')  order by jday desc) where rownum < 2", "dxqk"));
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "jddx" + time.ToString();


            return num;
        }
        public string hxkc()
        {
            string cday = DateTime.Now.ToShortDateString().ToString();
            string etime = cday + "08:00:00";
            string btime = Convert.ToDateTime(cday).AddHours(-4).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":btime", OracleType.VarChar, btime),
                DALHelper.MakeInParam(":etime", OracleType.VarChar, etime)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select * from (select hx from dd.JDDX where jday >= to_date(:btime,'yyyy-mm-dd hh24:mi:ss') and jday <= to_date(:etime,'yyyy-mm-dd hh24:mi:ss') order by jday desc) where rownum < 2", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
    }
}
